/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.ssi.mrms.dao.Impl;

import com.ssi.mrms.dao.CarStickerDao;
import com.ssi.mrms.dao.mapping.CarStickerDetailMapping;
import com.ssi.mrms.model.DataTableModel;
import com.ssi.mrms.model.RMcsdtlModel;
import com.ssi.mrms.model.TowerModel;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.MessageFormat;
import static java.text.MessageFormat.format;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

/**
 *
 * @author jirapatjantsakool
 */
@Repository
public class CarStickerDaoImpl implements CarStickerDao {

    @Autowired
    JdbcTemplate jdbcTemplate;
    
    private static final Logger logger = Logger.getLogger(CarStickerDaoImpl.class);

    private static final String SQL_QUERY_RMCSDTL_ALL = "SELECT * FROM rmcsdtl WHERE 1=1 ";
    private static final String SQL_QUERY_RMCSDTL_BY_ID = "SELECT cp.*, rm.* FROM rmcsdtl cp INNER JOIN rmrmlst rm ON cp.rlrmid = rm.rlrmid where cp.csdtlid = ? ";

    @Override
    public List<RMcsdtlModel> findAll() {

        List<RMcsdtlModel> list = jdbcTemplate.query(SQL_QUERY_RMCSDTL_ALL, new Object[]{}, new CarStickerDetailMapping());

        return list == null ? new ArrayList<RMcsdtlModel>() : list;
    }

    @Override
    public RMcsdtlModel findById(Integer id) {
        List<RMcsdtlModel> model = jdbcTemplate.query(SQL_QUERY_RMCSDTL_BY_ID, new Object[]{id}, new CarStickerDetailMapping());

        return model == null ? new RMcsdtlModel() : model.get(0);
    }

    @Override
    public void create(final RMcsdtlModel model) throws Exception {
        
        final String SQL_INSERT_RMCSDTL = "INSERT INTO rmcsdtl(csdtlid,rlrmid,csdcarplt,csdrcvby,csdrcvdat,csdtyp,csdstat,csdremark,csdcreby,csdcredat,csdupdby,csdupddat) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)";

        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(new PreparedStatementCreator() {

            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(format(SQL_INSERT_RMCSDTL), Statement.RETURN_GENERATED_KEYS);
                int i = 0;
                ps.setInt(++i, model.getCsdtlid());
                ps.setInt(++i, model.getRlrmid());
                ps.setString(++i, model.getCsdcarplt()== null ? null : model.getCsdcarplt());
                ps.setString(++i, model.getCsdrcvby()== null ? null : model.getCsdrcvby());
                ps.setDate(++i, model.getCsdrcvdat() == null ? null : new java.sql.Date(model.getCsdrcvdat().getTime()));
                ps.setString(++i, model.getCsdtyp()== null ? null : model.getCsdtyp());
                ps.setString(++i, model.getCsdstat()== null ? null : model.getCsdstat());
                ps.setString(++i, model.getCsdremark()== null ? null : model.getCsdremark());
                ps.setString(++i, model.getCsdcreby()== null ? null : model.getCsdcreby());
                ps.setDate(++i, model.getCsdcredat() == null ? null : new java.sql.Date(model.getCsdcredat().getTime()));
                ps.setString(++i, model.getCsdupdby()== null ? null : model.getCsdupdby());
                ps.setDate(++i, model.getCsdupddat()== null ? null : new java.sql.Date(model.getCsdupddat().getTime()));
                return ps;
            }

        }, keyHolder);
    }

    private static final String SQL_UPDATE_RMCSDTL = "UPDATE rmcsdtl SET rlrmid=?,csdcarplt=?,csdrcvby=?,csdrcvdat=?,csdtyp=?,csdstat=?,csdremark=?,csdcreby=?,csdcredat=?,csdupdby=?,csdupddat=? WHERE csdtlid=?";
                         
    @Override
    public boolean update(RMcsdtlModel model) {
        int row = jdbcTemplate.update(format(SQL_UPDATE_RMCSDTL),
                new Object[]{
                    model.getRlrmid(),
                    model.getCsdcarplt(),
                    model.getCsdrcvby(),
                    model.getCsdrcvdat(),
                    model.getCsdtyp(),
                    model.getCsdstat(),
                    model.getCsdremark(),
                    model.getCsdcreby(),
                    model.getCsdrcvdat(),
                    model.getCsdupdby(),
                    model.getCsdupddat(),
                    model.getCsdtlid()
                });
        return row > 0;
    }

    private static final String SQL_DELETE_RMCSDTL = "DELETE FROM rmcsdtl WHERE csdtlid=?";

    @Override
    public boolean delete(RMcsdtlModel model) {
        int row = jdbcTemplate.update(format(SQL_DELETE_RMCSDTL), new Object[]{model.getCsdtlid()});
        return row > 0;
    }
    
    @Override
    public DataTableModel<RMcsdtlModel> findDataTable(RMcsdtlModel model, int page, int perPage) {
       	
    	DataTableModel<RMcsdtlModel> dataTableModel = new DataTableModel<RMcsdtlModel>();
        String sqlLimit = MessageFormat.format(" limit {0},{1} ", new Object[]{page, perPage});
        
        TowerModel tower = model.getTower();

        /* --- Where Condition  ---*/
        String sql_where = ""; 
        String sql_join = " LEFT JOIN RMRMLST TW ON TW.RLRMID = ";
        /* --- Room Condition  ----*/
        if (StringUtils.isNotBlank(tower.getTower())) {
            sql_where = sql_where.concat(" AND TW.RLSTTOWER = '" + tower.getTower() + "' ");
        }
        if (StringUtils.isNotBlank(tower.getFloor())) {
        	sql_where = sql_where.concat(" AND TW.RLSTFLOOR = '" + tower.getFloor() + "' ");
        }
        if (StringUtils.isNotBlank(tower.getUnitNo())) {
        	sql_where = sql_where.concat(" AND TW.RLSTUNITNO = '" + tower.getUnitNo() + "' ");
        }
        if (StringUtils.isNotBlank(tower.getAddress())) {
        	sql_where = sql_where.concat(" AND TW.RLSTADDR = '" + tower.getAddress() + "' ");
        }
        
        /*--- Other Condition ----*/
        if (StringUtils.isNotBlank(model.getCsdcarplt())) {
        	sql_where = sql_where.concat(" AND A.CSDCARPLT LIKE '%" + model.getCsdcarplt() + "%' ");
        }
        if (StringUtils.isNotBlank(model.getCsdstat())) {
        	sql_where = sql_where.concat(" AND A.CSDSTAT LIKE '%" + model.getCsdstat() + "%' ");
        }
        if (StringUtils.isNotBlank(model.getCsdtyp())) {
            sql_where = sql_where.concat(" AND A.CSDTYP = '" + model.getCsdtyp() + "' ");
        }
        
         /*--- Initial Query ---*/
		String sqlQuery = " SELECT A.* , TW.* FROM rmcsdtl A " + sql_join +" A.RLRMID " +
				" WHERE 1=1 " + sql_where + sqlLimit;
		String sqlCount = "SELECT COUNT(1) FROM rmcsdtl A " + sql_join +" A.RLRMID " +
				" WHERE 1=1"  + sql_where;
		
		// --- #1 Get data for datatable by limit row
		List<RMcsdtlModel> list = jdbcTemplate.query(sqlQuery, new CarStickerDetailMapping());
		
		// --- #2 Get row number
		int count = jdbcTemplate.queryForInt(sqlCount);
		
        dataTableModel.setItems(list);
        dataTableModel.setTotal(count);
        dataTableModel.setIndex(page);
        dataTableModel.setLength(perPage);
        
        logger.debug("SQL : " + sqlQuery);
		return dataTableModel;
        
    }

}
